from datetime import datetime
from typing import Optional

from sqlalchemy import text
from sqlalchemy.orm import Session

from core.dao.cms_car_dao import CmsCarDao
from core.dao.cms_order_execute_dao import CmsOrderExecuteDao
from core.entity.do.models import CmsTerminalPoint, CmsOrderExecute
from core.entity.vo.view_models import CmsTerminalPointVO
from core.entity.do.cms_car import CmsCar


class CmsTerminalPointDao:
    """
    高德终端dao
    """

    @classmethod
    def get_cms_terminal_point_by_id(cls, db: Session, id: int):
        """
        根据id查询高德终端信息
        :param db: orm对象
        :param id: 主键ID
        :return: 高德终端信息对象
        """
        cms_terminal_point = db.query(CmsTerminalPoint) \
            .filter(CmsTerminalPoint.id == id) \
            .first()

        return cms_terminal_point

    @classmethod
    def get_cms_terminal_point_by_terminal_id(
            cls, db: Session, terminal_id: str, start_time: Optional[datetime] = None,
            end_time: Optional[datetime] = None
    ):
        sql_text = """
        SELECT
        id,
        terminal_id,
        height,
        speed,
        direction,
        up_time,
        state,
        create_time,
        create_by,
        update_time,
        update_by,
        device_id, 
            CAST(ST_X(transformed_geom) AS numeric) AS lon,  
            CAST(ST_Y(transformed_geom) AS numeric) AS lat  
        FROM (  
        SELECT
        p.id,
        p.terminal_id,
        p.height,
        p.speed,
        p.direction,
        p.up_time,
        p.state,
        p.create_time,
        p.create_by,
        p.update_time,
        p.update_by,
        d.car_device_no as device_id, 
        geoc_wgs84togcj02_point(ST_SetSRID(ST_MakePoint(p.lon::numeric, p.lat::numeric), 4326)) AS transformed_geom
        from cms_terminal_point p
        left join cms_device d on p.device_id = d.device_id 
        """
        sql_text = sql_text + f"where p.device_id='{terminal_id}' "
        if start_time:
            sql_text = sql_text + f"and p.up_time>'{start_time}' "
        if end_time:
            sql_text = sql_text + f"and p.up_time<'{end_time}' "
        sql_text = sql_text + ") AS subquery "
        sql = text(
            sql_text
        )
        cms_terminal_point_list = db.execute(sql).all()
        formatted_list = [
            {
                'device_id': cms_tp.device_id,
                'terminal_id': cms_tp.terminal_id,
                'lon': cms_tp.lon,
                'lat': cms_tp.lat,
                'height': cms_tp.height,  # 如果 height 是可选的，您可能还需要添加条件判断
                'speed': cms_tp.speed,  # 同上
                'direction': cms_tp.direction,  # 同上
                'up_time': cms_tp.up_time.strftime("%Y-%m-%d %H:%M:%S") if cms_tp.up_time else None,
                'create_time': cms_tp.create_time.strftime("%Y-%m-%d %H:%M:%S"),  # 注意这里我们假设 create_time 总是存在的
                'update_time': cms_tp.update_time.strftime("%Y-%m-%d %H:%M:%S") if cms_tp.update_time else None,
                'state': cms_tp.state,

            }
            for cms_tp in cms_terminal_point_list
        ]
        return formatted_list

    @classmethod
    def get_cms_terminal_point_by_terminal_id_last_one(
            cls, db: Session, terminal_id: str
    ):
        sql_text = """
        SELECT
        up_time,
        device_id, 
            CAST(ST_X(transformed_geom) AS numeric) AS lon,  
            CAST(ST_Y(transformed_geom) AS numeric) AS lat  
        FROM (  
        SELECT
        device_id,
        up_time,
        geoc_wgs84togcj02_point(ST_SetSRID(ST_MakePoint(lon::numeric, lat::numeric), 4326)) AS transformed_geom
        from cms_terminal_point
        """
        sql_text = sql_text + f"where device_id='{terminal_id}' ORDER BY up_time desc limit 1 ) AS subquery "
        sql = text(
            sql_text
        )
        distinct = db.execute(sql).first()
        point = CmsTerminalPoint()
        point.up_time = distinct.up_time
        point.device_id = distinct.device_id
        point.lon = distinct.lon
        point.lat = distinct.lat
        return point

    @classmethod
    def add_cms_terminal_point_dao(cls, db: Session, cms_terminal_point: CmsTerminalPointVO):
        """
        新增高德终端
        :param db: orm对象
        :param cms_terminal_point: 高德终端对象
        :return:
        """
        model_dict = CmsTerminalPoint(**cms_terminal_point.dict())
        db.add(model_dict)
        db.flush()

        return model_dict

    @classmethod
    def edit_cms_terminal_point_dao(cls, db: Session, cms_terminal_point: dict):
        """
        编辑参数配置数据库操作
        :param db: orm对象
        :param cms_terminal_point: 需要更新的参数配置字典
        :return:
        """
        db.query(CmsTerminalPoint) \
            .filter(CmsTerminalPoint.id == cms_terminal_point.get('id')) \
            .update(cms_terminal_point)

    @classmethod
    def delete_cms_terminal_point_dao(cls, db: Session, id: int):
        """
        编辑参数配置数据库操作
        :param db: orm对象
        :param id: id
        :return:
        """
        db.query(CmsTerminalPoint) \
            .filter(CmsTerminalPoint.id == id) \
            .delete()

    @classmethod
    def get_cms_terminal_point_by_order_id(cls, query_db: Session, order_id: str, start_time: datetime = None,
                                           end_time: datetime = None):
        """
        根据订单id查询高德终端信息，可选根据开始时间和结束时间过滤
        :param query_db: orm对象
        :param order_id: 订单id
        :param start_time: 开始时间（可选）
        :param end_time: 结束时间（可选）
        :return: 高德终端信息对象列表
        """
        order_execute: CmsOrderExecute = CmsOrderExecuteDao.get_one_by_order_id(query_db, order_id)
        if not order_execute:
            return []  # or raise an exception if you prefer
        car: CmsCar = CmsCarDao.get_car_by_car_id(query_db, order_execute.car_id)
        if not car:
            return []
        return CmsTerminalPointDao.get_cms_terminal_point_by_terminal_id(
            query_db,
            car.device_id,
            order_execute.reality_begin_time,
            order_execute.reality_end_time
        )
